package columbia.edu;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.pool.OracleDataSource;
/**
* Servlet implementation class OracleServlet
*/
public class DisplayDetailsServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private static final String connect_string =
//"jdbc:oracle:thin:scott/tiger@//w4111b.cs.columbia.edu:1521/ADB";
	"jdbc:oracle:thin:ab3416/kFxJssQk@//w4111b.cs.columbia.edu:1521/ADB";
	//"jdbc:oracle:thin:zzn2101/ycmrmMbM@//w4111b.cs.columbia.edu:1521/ADB";
private Connection conn;
/**
* @see HttpServlet#HttpServlet()
*/
public DisplayDetailsServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter pw = new PrintWriter(response.getOutputStream());
try {
if (conn == null) {
// Create a OracleDataSource instance and set URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(connect_string);
//conn = ods.getConnection();
conn = (Connection) request.getSession().getAttribute("Connection");
if (conn == null) { 
	conn = ods.getConnection();
	request.getSession().setAttribute("Connection",conn);
}
}
Statement stmt = conn.createStatement();
//ResultSet rset = stmt.executeQuery("select ENAME from EMP");
String custID = (String)(request.getSession().getAttribute("theName"));
custID=custID.trim();
int qty;
try {
	qty = Integer.parseInt(request.getParameter("qty"));
}
catch( Exception e) {
	qty=0;
}
request.getSession().setAttribute("qty", qty);
pw.println("<html>");
pw.println("<head><title>Please confirm your Details</title></head>");
pw.println("<link rel='stylesheet' href='style.css' type='text/css' charset='utf-8' />");
pw.println("<BR><BR>");
pw.println("<H1>Please confirm your Details </H1><BR>");
pw.println("<BR><BR>");
pw.println("<H2>Payment Info </H2><BR>");
pw.println("<BR>");
pw.println("<body>");

String pid = request.getSession().getAttribute("ID").toString();
ResultSet rset = stmt.executeQuery("select price from products where pid = '" + pid + "'");

Double price = 0.0;

if (rset.next()) {
	price = rset.getDouble("price");
}


price = price * qty;

pw.println("<H3>");
pw.println("Payment Amount $" + price + "<BR>");


rset = stmt.executeQuery("select cardtype, cardno, expdate from payment_info p, customers c where c.cust_id = p.cust_id and c.cust_id = '" + custID + "'"); 
response.setContentType("text/html");

//pw.println("ID " + ID);
//pw.println("<body><BR>");

while (rset.next()) {
//pw.println (rset.getString("ename") + "<BR>");
	pw.print("Card Type : ");
	pw.println (rset.getString("cardtype") + "<BR>");
	//pw.print(" ");
	pw.print("Card No : ");
	pw.println (rset.getString("cardno") + "<BR>");
	//pw.print(" ");
	pw.print("Exp Date : ");
	pw.println (rset.getString("expdate") + "<BR>");
	//pw.print(" ");
	
}
pw.println("</H3>");
pw.println("<BR><BR>");
pw.println("<H2>Shipping Address <BR></H2>");
pw.println("<BR>");
rset = stmt.executeQuery("select street, city, state, country, zip from address a, customers c where c.cust_id = a.cust_id and a.type_of_addr = 'S' and c.cust_id = '" + custID + "'");  
response.setContentType("text/html");
pw.println("<H3>");
while (rset.next()) {
	//pw.println (rset.getString("ename") + "<BR>");
	//	pw.print("Card Type : ");
		pw.println (rset.getString("street"));
		//pw.print(" ");
		//pw.print("Card No : ");
		pw.println (" " + rset.getString("city"));
		//pw.print(" ");
		//pw.print("Exp Date : ");
		pw.println (" " + rset.getString("state"));
		pw.println (" " + rset.getString("country"));
		//pw.print(" ");
		pw.println (" " + rset.getString("zip"));
		
	}
pw.println("</H3>");


pw.println("<form action='RecordOrderDetails'>");
pw.println("<br><input type='submit' value='Place Your Order'</br>");
pw.println("</form>");
pw.println("<div id='nav-top'>");
pw.println("<ul>");
pw.println("<li><a href='Logout.jsp'>Logout</a> |</li>");
//pw.println("<li><a href='Account.jsp'>My Account</a> |</li>");
pw.println("<li><a href='Contact.jsp'>Contact us</a></li>");
pw.println("</ul>");
pw.println("</div>");


pw.println("</body></html>");
} catch (SQLException e) {
pw.println(e.getMessage());
}
pw.close();
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
}
}